package tool;

import java.io.*;
import java.nio.file.*;
import java.util.*;

public class MySQLToSQLiteConverter {

    public static void main(String[] args) {
        // 输入 MySQL 脚本文件的路径
        String inputFilePath = "D:\\Information_technology\\workspace_study_demo\\springboot-improve-road\\2.x\\springboot-database\\springboot-sqlite-jdbc\\doc\\input.sql";
        // 输出 SQLite 脚本文件的路径
        String outputFilePath = "D:\\Information_technology\\workspace_study_demo\\springboot-improve-road\\2.x\\springboot-database\\springboot-sqlite-jdbc\\doc\\output.sql";

        try {
            List<String> mysqlLines = Files.readAllLines(Paths.get(inputFilePath));
            List<String> sqliteLines = new ArrayList<>();

            for (String line : mysqlLines) {
                // 处理 CREATE TABLE 语句
                if (line.trim().startsWith("CREATE TABLE")) {
                    line = convertCreateTable(line);
                }

                // 转换所有数据类型为 TEXT
                line = convertDataTypesToText(line);

                // 移除不支持的语法和 MySQL 特有的语法
                line = removeUnsupportedSyntax(line);
                line = removeMySQLSpecificSyntax(line);

                sqliteLines.add(line);
            }

            // 写入 SQLite 格式的脚本
            Files.write(Paths.get(outputFilePath), sqliteLines);
            System.out.println("MySQL 脚本已成功转换为 SQLite 脚本！输出路径：" + outputFilePath);

        } catch (IOException e) {
            System.err.println("文件读取/写入时出错：" + e.getMessage());
        }
    }

    // 转换 CREATE TABLE 语句
    private static String convertCreateTable(String line) {
        return line.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
    }

    // 转换所有数据类型为 TEXT
    private static String convertDataTypesToText(String line) {
        return line.replaceAll("(?i)(varchar\\(\\d+\\)|decimal\\(\\d+,\\d+\\)|int\\(\\d+\\)|datetime|timestamp|text|double|float)", "TEXT");
    }

    // 移除 MySQL 中 SQLite 不支持的语法
    private static String removeUnsupportedSyntax(String line) {
        // 移除 `ON UPDATE CURRENT_TIMESTAMP` 等 MySQL 特有的语法
        line = line.replaceAll("(?i)ON UPDATE CURRENT_TIMESTAMP", "");
        return line;
    }

    // 移除 MySQL 特定的语法，如索引、字符集等
    private static String removeMySQLSpecificSyntax(String line) {
        line = line.replaceAll("(?i)CHARSET = [a-zA-Z0-9]+", ""); // 删除 CHARSET 设置
        line = line.replaceAll("(?i)DEFAULT CHARSET = [a-zA-Z0-9]+", ""); // 删除默认 CHARSET
        line = line.replaceAll("(?i)ROW_FORMAT = [a-zA-Z0-9]+", ""); // 删除 ROW_FORMAT
        line = line.replaceAll("(?i)COMMENT '.*'", ""); // 删除 COMMENT
        line = line.replaceAll("(?i)BLOCK_SIZE = \\d+", ""); // 删除 BLOCK_SIZE

        // SQLite 不支持存储引擎、压缩算法等
        line = line.replaceAll("(?i)ENGINE = [a-zA-Z]+", "");
        line = line.replaceAll("(?i)REPLICA_NUM = \\d+", "");
        line = line.replaceAll("(?i)COMPRESSION = '[^']+'", "");
        line = line.replaceAll("(?i)USE_BLOOM_FILTER = (TRUE|FALSE)", "");
        line = line.replaceAll("(?i)TABLET_SIZE = \\d+", "");
        line = line.replaceAll("(?i)PCTFREE = \\d+", "");

        return line;
    }
}
